/*******************************************************************************
 * This file is part of blueprint4j www.blueprint4j.org.
 * 
 *     blueprint4j is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU Lesser General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     blueprint4j is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with blueprint4j. If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package blueprint4j.db;

import java.sql.*;
import java.util.Date;

import blueprint4j.utils.*;

import java.text.SimpleDateFormat;

public class DataBaseUtilsInformix extends DataBaseUtils {
	private java.text.SimpleDateFormat simple_date_format = new java.text.SimpleDateFormat( "yyyy-MM-dd" );
	private java.text.SimpleDateFormat simple_date_and_time_format = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss.SSS" );
	private java.text.SimpleDateFormat simple_time_format = new java.text.SimpleDateFormat( "HH:mm:ss.SSS" );


	public SimpleDateFormat getDateTimeFormat() {
		return simple_date_and_time_format;
	}

	public SimpleDateFormat getTimeFormat() {
		return simple_time_format;
	}

	public SimpleDateFormat getDateFormat() {
		return simple_date_format;
	}


	public Long getNextIndexField(Entity entity)
	throws SQLException {
		Statement statement = DBTools.getLocalConnection(entity.getConnection()).createStatement();
		statement.execute("insert into " + IndexTable.TABLE_NAME + "(tablename) values ('"+entity.getTableName()+"')");
		ResultSet result_set = statement.executeQuery("select first 1 dbinfo('sqlca.sqlerrd1') from " + IndexTable.TABLE_NAME);
		if (!result_set.next()) {
			throw new SQLException("Could not read the next inserted index field");
		}
		statement.close();
		return new Long(result_set.getLong(1));
	}

	public void loadIdAfterInsert(DBConnection connection,FieldId id)
	throws SQLException {
		throw new SQLException("NOT Implemented");
	}

	public void createIndexTable(Statement st)
	throws SQLException {
		st.execute("create table d_index_table(index_field bigint unsigned AUTO_INCREMENT,tablename varchar(64) not null,primary key(index_field))");
	}

	/**
	* Convert a Date to SQL leave out the time
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return " DATETIME(" + simple_date_format.format(date) + ") YEAR TO DAY ";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value without time
	*/
	public String currentDateSQLMethod() {
		return "TODAY";
	}

	/**
	* Convert a Date to SQL including time
	*
	* @param date the DateTime to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return " DATETIME(" + simple_date_and_time_format.format(date) + ") YEAR TO FRACTION ";
	}

	public String convertTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return " DATETIME(" + simple_date_and_time_format.format(date) + ") YEAR TO FRACTION ";
	}
	
	public String castTime(String date) {
		return "cast("+date+" AS TIME)";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value with time
	*/
	public String currentDateTimeSQLMethod() {
		return "CURRENT";
	}

	public String dateSUB_HOUR(Date date,int hours) {
		Log.critical.out(new Exception("METHOD dateSUB_HOUR NOT IMPLMENTED FOR MSSQL"));
		return null;
	}

	public String dateSUB_MIN(Date date,int min) {
		Log.critical.out(new Exception("METHOD dateSUB_MIN NOT IMPLMENTED FOR MSSQL"));
		return null;
	}

	public String dateSUB_SEC(Date date,int sec) {
		Log.critical.out(new Exception("METHOD dateSUB_SEC NOT IMPLMENTED FOR MSSQL"));
		return null;
	}
	
	public boolean mayUpdateFromResultSet(DBConnection dbcon) throws SQLException {
		return false;
	}

	/**
	* Get the SQL command for top or first
	*
	* @param number The number of rows you would like back from the select.
	* @return Returns the SQL string command to limit the number of rows returned in a system
	*/
	public String sqlFirstString(String sql,int number) {
		return " FIRST " + number + " " + sql;
	}

	/**
	* Converts a string value to a suitable value for an SQL statement
	*
	* @param source The value of the string
	* @return A converted string ready for use in SQL
	*/
	public String convertStringForSQL(String source) {
		if (source == null) return null;
		//source =source.replaceAll("\n","<LF>").replaceAll("\r","<CR>");
		//source=replaceParts(source,new String[][]{{"\n","<LF>"},{"\r","<CR>"},{"'","''"}});
		source=replaceAll(source,"\r","<CR>");
		source=replaceAll(source,"\n","<LF>");
		source=replaceAll(source,"'","<PP>");
		source=replaceAll(source,"<PP>","''");
		source = "'" + source + "'";
		int inserted = 0;
		for (int i = 0;i < source.length();i++) {
			if (i != 0 && i % ((250) * (inserted+1) + inserted*4) == 0) {
				inserted++;
				source = source.substring(0,i) + "'||'" + source.substring(i);
			}
		}
		return source;
	}

	/**
	* Converts a string after is has been returned from the database
	*
	* @param source The value of the string from the database
	* @return A converted string converted from the SQL syntax
	*/
	public String convertStringFromSQL(String source){
		if (source==null) return null;

		source = replaceAll(source,"<LF>","\n");
		source = replaceAll(source,"<CR>","\r");
		return source.trim();
	}

	public void executeKeepAliveSQL(DBConnection connection)
	throws SQLException {
		Statement smt = connection.createStatement();
		smt.executeQuery("select 1 = 1");
		smt.close();
	}

	public void executeOptimizeTable(DBConnection connection,String table_name)
	throws SQLException {
		throw new DataException("METHOD executeOptimizeTable NOT IMPLEMENTED");
	}

	/*
	 * Build All the sql fields from the MetaData
	 *
	public Field[] buildFieldsFromMetaData(ResultSetMetaData meta_data) throws SQLException,DataException {
		return null;
}*/

	public void createMaintananceTable(Connection connection) throws SQLException {
		throw new SQLException("createMaintananceTable Not implemented yet");
	}

	void createTable(Entity entity)
	throws DataException,SQLException {
		throw new DataException("Method createTable not implmented yet");
	}

	public void dropIndex(DBConnection connection,String table_name,String index_name)
	throws SQLException,DataException {
		throw new DataException("Method dropIndex not implmented yet");
	}

	public Index.VectorIndex getIndexForTable(DBConnection connection,String table_name)
	throws SQLException {
		throw new DataException("Method getIndexForTable not implmented yet");
	}

	public void setupDatabase(DBConnection connection)
	throws SQLException {}

	public String getSQLType(FieldAutoId field){return "bigint AUTO_INCREMENT";}
	public String getSQLType(FieldBigDecimal field) {return "double";}
	public String getSQLType(FieldBigInteger field){return "bigint";}
	public String getSQLType(FieldBoolean field){return "char(1)";}
	public String getSQLType(FieldCharacter field){return "char(1)";}
	public String getSQLType(FieldColor field){return "char(6)";}
	public String getSQLType(FieldCurrency field){return "decimal(10,2)";}
	public String getSQLType(FieldDate field){return "datetime";}
	public String getSQLType(FieldDateTime field){return "datetime";}
	public String getSQLType(FieldDouble field){return "double";}
	public String getSQLType(FieldInteger field){return "int";}
	public String getSQLType(FieldLong field){return "bigint";}
	public String getSQLType(FieldString field){return "varchar("+field.getLength()+")";}
	public String getSQLType(FieldTime field){return "datetime";}
	public String getSQLType(FieldText field){return "text";}
	public String getSQLType(FieldImage field){return "blob";}
	public String getSQLType(FieldProperties field){return "text";}
	public String getSQLType(FieldBlob field){return "blob";}

	public void addField(DBConnection dbcon,Field field)
	throws SQLException {
		dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
	}

	public void modifyTableField(DBConnection connection,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			connection.execute("alter table " + field.getEntity().getTableName() + " modify column " + field.getName() + " " + getSQLType(field) +" not null");
		} else {
			connection.execute("alter table " + field.getEntity().getTableName() + " modify column " + field.getName() + " " + getSQLType(field));
		}
	}

	public void modifyTableField(DBConnection connection,String old_name,Field field)
	throws SQLException {
		throw new SQLException("NOT CODED YET");
	}

	public String getDropField(String name,String tablename) {
		return "alter table " + tablename + " drop column " + name;
	}

	public void setupDatabaseServer(DBConnection connect) throws SQLException {}

	public boolean createDatabase(String path,String dbname,String username,String password)  {
		return false;
	}

	public void bulkLoadFile(DBConnection dbcon,String tablename,java.io.File file,String delimiter)
	throws SQLException {
		throw new SQLException("METHOD NOT IMPLEMENTED YET");
	}

	public void exportData(DBConnection dbcon,String select,java.io.File bulkfile)
	throws SQLException {
		throw new DataException("Not implemented yet");
	}

	public void backupDB(DBConnection dbcon,java.io.File file) throws Throwable {}
	public void restoreDB(DBConnection dbcon,java.io.File file) throws Throwable {}
}

